{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": false
   },
   "source": [
    "# 服务器上下载urule log日志"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-04-01T03:08:27.358007Z",
     "start_time": "2024-04-01T03:03:04.218512Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2024-03-20,(16477, 8),查询耗时9.10482382774353\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AM_type3_2024-03-20.parquet (138, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AM_type1_2024-03-20.parquet (1151, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AM_type2_2024-03-20.parquet (7243, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AG_type1_2024-03-20.parquet (237, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AG_type2_2024-03-20.parquet (764, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AC_type1_2024-03-20.parquet (657, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AC_type2_2024-03-20.parquet (3063, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AEC_type2_2024-03-20.parquet (2219, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AL_type1_2024-03-20.parquet (457, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AL_type2_2024-03-20.parquet (548, 8)\n",
      "通过基本数据检查========>\n",
      "2024-03-21,(13763, 8),查询耗时9.510327577590942\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AM_type2_2024-03-21.parquet (6115, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AM_type3_2024-03-21.parquet (162, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AM_type1_2024-03-21.parquet (1088, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AC_type2_2024-03-21.parquet (2762, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AC_type1_2024-03-21.parquet (594, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AG_type2_2024-03-21.parquet (699, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AG_type1_2024-03-21.parquet (255, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AL_type2_2024-03-21.parquet (543, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AL_type1_2024-03-21.parquet (344, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AEC_type2_2024-03-21.parquet (1201, 8)\n",
      "通过基本数据检查========>\n",
      "2024-03-22,(14919, 8),查询耗时14.107778310775757\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AM_type2_2024-03-22.parquet (6904, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AM_type1_2024-03-22.parquet (1010, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AM_type3_2024-03-22.parquet (169, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AC_type2_2024-03-22.parquet (2897, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AC_type1_2024-03-22.parquet (652, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AL_type2_2024-03-22.parquet (536, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AL_type1_2024-03-22.parquet (396, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AG_type2_2024-03-22.parquet (993, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AG_type1_2024-03-22.parquet (481, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AEC_type2_2024-03-22.parquet (881, 8)\n",
      "通过基本数据检查========>\n",
      "2024-03-23,(15316, 8),查询耗时16.09730100631714\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AG_type1_2024-03-23.parquet (882, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AG_type2_2024-03-23.parquet (1507, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AM_type1_2024-03-23.parquet (1049, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AM_type2_2024-03-23.parquet (6444, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AM_type3_2024-03-23.parquet (159, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AL_type1_2024-03-23.parquet (409, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AL_type2_2024-03-23.parquet (607, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AC_type1_2024-03-23.parquet (629, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AC_type2_2024-03-23.parquet (2926, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AEC_type2_2024-03-23.parquet (704, 8)\n",
      "通过基本数据检查========>\n",
      "2024-03-24,(13298, 8),查询耗时15.027229309082031\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AM_type2_2024-03-24.parquet (6189, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AM_type1_2024-03-24.parquet (858, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AM_type3_2024-03-24.parquet (140, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AL_type2_2024-03-24.parquet (444, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AL_type1_2024-03-24.parquet (294, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AG_type2_2024-03-24.parquet (1284, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AG_type1_2024-03-24.parquet (724, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AEC_type2_2024-03-24.parquet (472, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AC_type2_2024-03-24.parquet (2344, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AC_type1_2024-03-24.parquet (549, 8)\n",
      "通过基本数据检查========>\n",
      "2024-03-25,(13837, 8),查询耗时25.790759563446045\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AG_type1_2024-03-25.parquet (603, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AG_type2_2024-03-25.parquet (1238, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AC_type1_2024-03-25.parquet (611, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AC_type2_2024-03-25.parquet (2550, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AM_type1_2024-03-25.parquet (925, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AM_type3_2024-03-25.parquet (133, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AM_type2_2024-03-25.parquet (5804, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AEC_type2_2024-03-25.parquet (1158, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AL_type1_2024-03-25.parquet (327, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AL_type2_2024-03-25.parquet (488, 8)\n",
      "通过基本数据检查========>\n",
      "2024-03-26,(15748, 8),查询耗时31.444105863571167\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AM_type2_2024-03-26.parquet (6304, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AM_type1_2024-03-26.parquet (966, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AM_type3_2024-03-26.parquet (156, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AG_type2_2024-03-26.parquet (1473, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AG_type1_2024-03-26.parquet (753, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AEC_type2_2024-03-26.parquet (1526, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AL_type2_2024-03-26.parquet (717, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AL_type1_2024-03-26.parquet (472, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AC_type2_2024-03-26.parquet (2774, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AC_type1_2024-03-26.parquet (607, 8)\n",
      "通过基本数据检查========>\n",
      "2024-03-27,(14920, 8),查询耗时14.486291646957397\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AC_type2_2024-03-27.parquet (2306, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AC_type1_2024-03-27.parquet (596, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AG_type2_2024-03-27.parquet (1748, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AG_type1_2024-03-27.parquet (831, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AM_type2_2024-03-27.parquet (5659, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AM_type1_2024-03-27.parquet (959, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AM_type3_2024-03-27.parquet (138, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AL_type2_2024-03-27.parquet (599, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AL_type1_2024-03-27.parquet (425, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AEC_type2_2024-03-27.parquet (1659, 8)\n",
      "通过基本数据检查========>\n",
      "2024-03-28,(13825, 8),查询耗时14.893316268920898\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AC_type1_2024-03-28.parquet (863, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AC_type2_2024-03-28.parquet (2305, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AEC_type2_2024-03-28.parquet (842, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AG_type1_2024-03-28.parquet (798, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AG_type2_2024-03-28.parquet (1608, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AM_type1_2024-03-28.parquet (945, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AM_type2_2024-03-28.parquet (5470, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AM_type3_2024-03-28.parquet (111, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AL_type1_2024-03-28.parquet (354, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AL_type2_2024-03-28.parquet (529, 8)\n",
      "通过基本数据检查========>\n",
      "2024-03-29,(15047, 8),查询耗时15.25692343711853\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AM_type2_2024-03-29.parquet (8080, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AM_type1_2024-03-29.parquet (761, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AM_type3_2024-03-29.parquet (86, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AC_type2_2024-03-29.parquet (2136, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AC_type1_2024-03-29.parquet (673, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AG_type2_2024-03-29.parquet (1335, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AG_type1_2024-03-29.parquet (701, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AEC_type2_2024-03-29.parquet (624, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AL_type2_2024-03-29.parquet (383, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AL_type1_2024-03-29.parquet (268, 8)\n",
      "通过基本数据检查========>\n",
      "2024-03-30,(13233, 8),查询耗时12.844963073730469\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AG_type2_2024-03-30.parquet (1877, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AG_type1_2024-03-30.parquet (902, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AC_type2_2024-03-30.parquet (2181, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AC_type1_2024-03-30.parquet (760, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AM_type2_2024-03-30.parquet (5242, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AM_type1_2024-03-30.parquet (961, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AM_type3_2024-03-30.parquet (117, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AL_type2_2024-03-30.parquet (414, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AL_type1_2024-03-30.parquet (270, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AEC_type2_2024-03-30.parquet (509, 8)\n",
      "通过基本数据检查========>\n",
      "2024-03-31,(13447, 8),查询耗时67.62296056747437\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AM_type2_2024-03-31.parquet (5221, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AM_type1_2024-03-31.parquet (1066, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AM_type3_2024-03-31.parquet (134, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AC_type2_2024-03-31.parquet (2256, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AC_type1_2024-03-31.parquet (775, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AL_type2_2024-03-31.parquet (465, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AL_type1_2024-03-31.parquet (271, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AG_type2_2024-03-31.parquet (1924, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AG_type1_2024-03-31.parquet (903, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AEC_type2_2024-03-31.parquet (432, 8)\n",
      "通过基本数据检查========>\n",
      "2024-04-01,(8253, 8),查询耗时9.078260898590088\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AC_type1_2024-04-01.parquet (387, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AC_type2_2024-04-01.parquet (1367, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AG_type1_2024-04-01.parquet (451, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AG_type2_2024-04-01.parquet (1016, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AEC_type2_2024-04-01.parquet (436, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AM_type1_2024-04-01.parquet (723, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AM_type2_2024-04-01.parquet (3354, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AM_type3_2024-04-01.parquet (122, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AL_type1_2024-04-01.parquet (147, 8)\n",
      "/data/apps/data/risk/jupyterdir/data/urule_log/AL_type2_2024-04-01.parquet (250, 8)\n",
      "通过基本数据检查========>\n"
     ]
    }
   ],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "import time\n",
    "from sqlalchemy import create_engine\n",
    "\n",
    "\n",
    "def mysql_engine(host, port, user, passwd, db=None):\n",
    "    try:\n",
    "        engine = create_engine(f'mysql+pymysql://{user}:{passwd}@{host}:{port}/{db}')\n",
    "        return engine\n",
    "    except Exception as e:\n",
    "        print(f\"An error occurred: {e}\")\n",
    "mysql_rule = mysql_engine('172.20.1.186',3306,'longxiaolei','WWsCQ9TXG+BJBHlK','rule')\n",
    "from datetime import datetime, timedelta  \n",
    "\n",
    "# 文件夹中起止数据的下载时间，首次全量下载，后续可以调整为开始时间以介于更新时间的耗时\n",
    "start_date = datetime(2024, 3, 20)   \n",
    "# start_date = datetime(2024, 1, 28)   \n",
    "# start_date = datetime(2023, 10, 23)   \n",
    "\n",
    "end_date = datetime.today()  \n",
    "\n",
    "#  循环起止时间的日期，下载数据到相应的目录\n",
    "for single_day in (start_date + timedelta(days=x) for x in range(0, (end_date - start_date).days + 1)):  \n",
    "    # 格式化日期为字符串  \n",
    "    date_string = single_day.strftime(\"%Y-%m-%d\")  \n",
    "    next_day = single_day+timedelta(days=1)  #min(single_day+timedelta(days=1),end_date)\n",
    "    \n",
    "    next_day_str = next_day.strftime(\"%Y-%m-%d\")  \n",
    "    \n",
    "    sql = f\"\"\"select a.id,a.merchant_id,a.tx_id,a.req_data,a.resp_data,a.create_time,a.update_time, \n",
    "            case when a.type is null then b.type else a.type end as type\n",
    "    from risk.t_risk_urule_req_log a left join risk.t_risk_req_record b on a.tx_id = b.tx_id and a.merchant_id = b.merchant_id\n",
    "    where a.create_time >= '{date_string}' and a.create_time < '{next_day_str}' and a.tx_id is not null\n",
    "    \"\"\"\n",
    "    start_time = time.time()\n",
    "    df = pd.read_sql(sql,mysql_rule) # type: ignore\n",
    "    end_time = time.time()\n",
    "    print(f\"{date_string},{df.shape},查询耗时{end_time-start_time}\")\n",
    "    merchant_ids =  df['merchant_id'].unique()\n",
    "    types  = df['type'].unique()\n",
    "    len_arr = [0]\n",
    "    for merchant_id in merchant_ids : \n",
    "        for t in types:\n",
    "            # 注意在savepath中设定保存的路径和文件名\n",
    "            df_new = df[(df['merchant_id']==merchant_id)&(df['type']==t)]\n",
    "            if df_new.shape[0]>0:\n",
    "                len_arr.append(df_new.shape[0])\n",
    "                save_path = f\"{os.path.abspath('data/urule_log')}/{merchant_id}_type{t}_{date_string}.parquet\"\n",
    "                df_new.to_parquet(save_path,compression='zstd')\n",
    "                print(save_path,df_new.shape)\n",
    "    if sum(len_arr ) == df.shape[0]:\n",
    "        print('通过基本数据检查========>')\n",
    "    else:\n",
    "        print(\"^^^^^^^^^^^^^数据条数检查失败^^^^^^^^^^^^^\")"
   ]
  },
  {
   "cell_type": "code",
   "outputs": [],
   "source": [],
   "metadata": {
    "collapsed": false
   }
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "py311",
   "language": "python",
   "name": "py311"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
